package db;

import beans.Employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class EmployeeDAO extends MySQLConnectionBean{

    private MySQLConnectionBean connection;

    public EmployeeDAO() {
        connection = new MySQLConnectionBean();
    }

    // 验证员工登录
    public Employee validateEmployeeLogin(String username, String password) {
        String sql = "SELECT * FROM employees WHERE EmployeeID = ? AND Password = ?";
        try (Connection conn = connection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, Integer.parseInt(username));  // 将字符串转换为整数
            pstmt.setString(2, password);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return extractEmployeeFromResultSet(rs);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 添加员工信息
    public int addEmployee(Employee employee) {
        String sql = "INSERT INTO employees (EmployeeName, Password, DepartmentID, Position, Email, PhoneNumber, HireDate, Gender) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (
                Connection conn = connection.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, employee.getEmployeeName());
            pstmt.setString(2, employee.getPassword());
            pstmt.setInt(3, employee.getDepartmentID());
            pstmt.setString(4, employee.getPosition());
            pstmt.setString(5, employee.getEmail());
            pstmt.setString(6, employee.getPhoneNumber());
            pstmt.setDate(7, new java.sql.Date(employee.getHireDate().getTime()));
            pstmt.setString(8, employee.getGender());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    // 根据员工ID查询员工信息
    public Employee getEmployeeByID(int employeeID) {
        String sql = "SELECT * FROM employees WHERE EmployeeID = ?";
        try (
                Connection conn = connection.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, employeeID);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return extractEmployeeFromResultSet(rs);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 更新员工信息
    public int updateEmployee(Employee employee) {
        String sql = "UPDATE employees SET EmployeeName = ?, Password = ?, DepartmentID = ?, " +
                    "Position = ?, Email = ?, PhoneNumber = ?, HireDate = ?, Gender = ? " +
                    "WHERE EmployeeID = ?";
        try (
                Connection conn = connection.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, employee.getEmployeeName());
            pstmt.setString(2, employee.getPassword());
            pstmt.setInt(3, employee.getDepartmentID());
            pstmt.setString(4, employee.getPosition());
            pstmt.setString(5, employee.getEmail());
            pstmt.setString(6, employee.getPhoneNumber());
            pstmt.setDate(7, new java.sql.Date(employee.getHireDate().getTime()));
            pstmt.setString(8, employee.getGender());
            pstmt.setInt(9, employee.getEmployeeID());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    // 从ResultSet提取Employee对象
    private Employee extractEmployeeFromResultSet(ResultSet rs) throws SQLException {
        Employee employee = new Employee();
        employee.setEmployeeID(rs.getInt("EmployeeID"));
        employee.setEmployeeName(rs.getString("EmployeeName"));
        employee.setPassword(rs.getString("Password"));
        employee.setDepartmentID(rs.getInt("DepartmentID"));
        employee.setPosition(rs.getString("Position"));
        employee.setEmail(rs.getString("Email"));
        employee.setPhoneNumber(rs.getString("PhoneNumber"));
        employee.setHireDate(rs.getDate("HireDate"));
        employee.setGender(rs.getString("Gender"));
        return employee;
    }

    // 新增方法：更新员工密码
    public boolean updatePassword(int employeeId, String newPassword) throws SQLException {
        String sql = "UPDATE employees SET Password = ? WHERE EmployeeID = ?";
        try (
                Connection conn = connection.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, newPassword);
            pstmt.setInt(2, employeeId);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e; // 重新抛出异常，让Service层处理
        }
    }
}